package model;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import utils.CustomThreadPool;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.time.Duration;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;

public class Users {
    Connection conn;
    String url = "jdbc:mysql://47.113.145.215:3316/shopping?useUnicode=true&characterEncoding=GBK&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true";
    String userName = "root";
    String passWord = "qazplm123";
    String sql = "INSERT INTO file_users (username, address, cell_phone_number, reserved_account_money, id, role, realname, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    String sql1 = "INSERT INTO file_goods (SerialNumber, ProductName, Brand, Price, Inventory, ParentCategory) VALUES (?, ?, ?, ?, ?, ?)";
    CustomThreadPool ctp;

    public Users() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, passWord);
            conn.setAutoCommit(false);

        } catch (Exception e) {
            e.printStackTrace();
        }
        // todo 修改线程数量
        ctp = new CustomThreadPool(30);

    }

    private void writeSqL(JSONArray jsonArray) {

        List<Future<?>> futures = new ArrayList<>();
        int batchSize = 1000; // 每个任务处理的数据量
        for (int i = 0; i < jsonArray.size(); i += batchSize) {
            final int start = i;
            final int end = Math.min(start + batchSize, jsonArray.size());

            Future<?> future = ctp.submit(() -> {
                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    for (int j = start; j < end; j++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(j);
                        pstmt.setString(1, jsonObject.getString("username"));
                        pstmt.setString(2, jsonObject.getString("address"));
                        pstmt.setString(3, jsonObject.getString("cell_phone_number"));
                        pstmt.setDouble(4, jsonObject.getDoubleValue("reserved_account_money"));
                        pstmt.setString(5, jsonObject.getString("id"));
                        pstmt.setString(6, jsonObject.getString("role"));
                        pstmt.setString(7, jsonObject.getString("realname"));
                        pstmt.setString(8, jsonObject.getString("password"));

                        pstmt.addBatch();

                        if ((j - start + 1) % batchSize == 0 || j == end - 1) {
                            pstmt.executeBatch();
                        }
                    }
                    conn.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
            futures.add(future);
        }

        // 等待所有任务完成
        for (Future<?> future : futures) {
            try {
                future.get();
            } catch (InterruptedException e) {
                e.printStackTrace();
            } catch (ExecutionException e) {
                e.printStackTrace();
            }
        }
    }

    private void writeSqL1(JSONArray jsonArray) {

        List<Future<?>> futures = new ArrayList<>();
        int batchSize = 1000; // 每个任务处理的数据量
        for (int i = 0; i < jsonArray.size(); i += batchSize) {
            final int start = i;
            final int end = Math.min(start + batchSize, jsonArray.size());

            Future<?> future = ctp.submit(() -> {
                try (PreparedStatement pstmt = conn.prepareStatement(sql1)) {
                    for (int j = start; j < end; j++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(j);
                        pstmt.setString(1, jsonObject.getString("SerialNumber"));
                        pstmt.setString(2, jsonObject.getString("ProductName"));
                        pstmt.setString(3, jsonObject.getString("Brand"));
                        pstmt.setString(4, jsonObject.getString("Price"));
                        pstmt.setString(5, jsonObject.getString("Inventory"));
                        pstmt.setString(6, jsonObject.getString("ParentCategory"));

                        pstmt.addBatch();

                        if ((j - start + 1) % batchSize == 0 || j == end - 1) {
                            pstmt.executeBatch();
                            if ((j - start + 1) % 5000 == 0 || j == end - 1) {
                                // todo 修改每多少次 提交一次 修改3000
                                conn.commit();
                            }
                        }

                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
            futures.add(future);
        }

        // 等待所有任务完成
        for (Future<?> future : futures) {
            try {
                future.get();
            } catch (InterruptedException e) {
                e.printStackTrace();
            } catch (ExecutionException e) {
                e.printStackTrace();
            }
        }

    }

    public void start() {
        List<Thread> ts = new ArrayList<>();
        LocalDateTime startTime = LocalDateTime.now();
        String path1 = Objects.requireNonNull(Users.class.getResource("/json/tt.json")).getPath();
        String path2 = Objects.requireNonNull(Users.class.getResource("/json/accounts.json")).getPath();
        System.out.println(path1 +"  " + path2);
        Thread t1 = new Thread(new Runnable() {
            @Override
            public void run() {
                writeSqL1(read(path1));
            }
        });
        t1.start();
        ts.add(t1);

        Thread t = new Thread(new Runnable() {
            @Override
            public void run() {
                writeSqL(read(path2));
            }
        });
        t.start();
        ts.add(t);


        for (Thread tt : ts) {
            try {
                tt.join();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

        LocalDateTime currentDateTime = LocalDateTime.now();
        System.out.println(Duration.between(startTime, currentDateTime).toMillis());


        // 查询两张表的行数
        try (PreparedStatement p = conn.prepareStatement("select COUNT(*) as row_count from file_users");
             PreparedStatement p1 = conn.prepareStatement("select COUNT(*) as row_count from file_goods")) {
            ResultSet resultSet = p.executeQuery();
            if (resultSet.next()){
                System.out.println("file_users:"+resultSet.getInt("row_count"));
            }
            ResultSet resultSet1 = p1.executeQuery();
            if (resultSet1.next()){
                System.out.println("file_goods:"+resultSet1.getInt("row_count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 删除两张表的数据
        try (PreparedStatement p = conn.prepareStatement("delete from file_users");
             PreparedStatement p1 = conn.prepareStatement("delete from file_goods")) {
            p.executeUpdate();
            p1.executeUpdate();
            conn.commit();
            System.out.println("已经清空数据");
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    private JSONArray read(String path) {
        StringBuilder result = new StringBuilder();
        try (BufferedReader reader = new BufferedReader(new FileReader(path))) {
            String line;

            while ((line = reader.readLine()) != null) {
                result.append(System.lineSeparator() + line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        String s = result.toString();
        return JSON.parseArray(s);

    }


}
